do "E:\yungyu\lottery_baby\do\set_environment.do"

clear
set more off

cap program drop summary_stat
program define summary_stat

syntax [if] [in] [aw fw iw pw], col(string) save(string)

	preserve

	marksample touse
	qui keep if `touse'

	collapse 	(mean)age (sd)age_sd = age ///
				(mean)resident_urban (sd)resident_urban_sd = resident_urban ///
				(mean)female (sd)female_sd = female ///
				(mean)married (sd)married_sd = married ///
				(mean)pers_labor (sd)pers_labor_sd = pers_labor ///
				(mean)pers_earnings (sd)pers_earnings_sd = pers_earnings ///
				(mean)pers_income (sd)pers_income_sd = pers_income ///	
				(mean)pers_asset (sd)pers_asset_sd = pers_asset ///
				(mean)pers_liquidasset (sd)pers_liquidasset_sd = pers_liquidasset ///
				(mean)pers_saving (sd)pers_saving_sd = pers_saving ///
				(mean)tot_earnings (sd)tot_earnings_sd = tot_earnings ///
				(mean)tot_income (sd)tot_income_sd = tot_income ///	
				(mean)tot_asset (sd)tot_asset_sd = tot_asset ///
				(mean)tot_liquidasset (sd)tot_liquidasset_sd = tot_liquidasset ///
				(mean)tot_saving (sd)tot_saving_sd = tot_saving ///						
				(mean)totbaby (sd)totbaby_sd = totbaby ///
				(mean)pre1born (sd)pre1born_sd = pre1born ///
				(mean)pre2born (sd)pre2born_sd = pre2born ///
				(mean)pre3born (sd)pre3born_sd = pre3born ///
				(count)N = n [aw=w]
	
	xpose, clear v
	order _var
	rename v1 `col'
	rename _var variables
	tostring `col', replace force format(%15.3fc)
	replace `col' = "(" + `col' + ")" if strpos(var,"_sd")~=0
	replace variables = "" if strpos(var,"_sd")~=0
	
	save "`save'", replace
	
	restore
end

use "$wdata\project_lottery_fertility_b3a6_first_5k.dta", clear

** Household variables	
foreach var in income earnings asset liquidasset saving{
	gen tot_`var' = pers_`var' + spouse_`var'
}


foreach var in saving{
	gen avg_`var' = pers_`var' if spouse_idn == ""
	replace avg_`var' = (pers_`var' + spouse_`var')/2 if spouse_idn ~= ""
}

forv i = 0(1)3{
	gen v1 = baby > 0 if norm == -`i'
	egen pre`i'born = mean(v1), by(houseid)
	drop v1
	gen v1 = pers_real_prize_aftertax if norm == -`i'
	egen pre`i'prize = mean(v1), by(houseid)
	drop v1
	recode pre`i'prize . = 0
}

gen resident_urban = resident_t == 1 | resident_m == 1
gen female = pers_gender == 2

gen pers_labor = pers_earnings > 0
gen avg_labor  = avg_earnings > 0	

gen prize = winyr_pers_real_prize_aftertax
replace prize = pre0prize if current == 0

//replace prize10M = prize10M * 10000
//replace prize10M = prize if current == 0

gen anyprize = prize>0

cap gen n = 1
gen w = 1

summary_stat if norm_year == -1 & prize10M <= 5 & current == 1, col(Current) save("$table/dta/TabC3_Current.dta")
summary_stat if norm_year == -1 & prize10M <= 5 & current == 0, col(Future) save("$table/dta/TabC3_Future.dta")


use "$table/dta/TabC3_Current.dta", clear
merge 1:1 _n using "$table/dta/TabC3_Future.dta", nogen

 
export excel "$table/Tables.xlsx", sheet("TabC3") sheetreplace first(var)
